package org.dong.samples.util;

import java.io.InputStream;
import java.io.NotActiveException;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dong.samples.model.vo.UserVO;
public class ExcelUtils {
	public static String OFFICE_EXCEL_2007_POX = ".xlsx";
	public static String OFFICE_EXCEL_2003_POX = ".xls";
	
	
	public static Workbook openWorkbook(InputStream inputStream, String pos) {
		Workbook hssfWorkbook = null;
		try{
			if(OFFICE_EXCEL_2003_POX.equals(pos)){
				hssfWorkbook = new HSSFWorkbook(inputStream);
			}else if(OFFICE_EXCEL_2007_POX.equalsIgnoreCase(pos)){
				hssfWorkbook = new XSSFWorkbook(inputStream);
			}
		}catch (Exception e) {
			if(e instanceof NotActiveException){
				return null;
			}
			e.printStackTrace();
		}
		
		return hssfWorkbook;
	}


	public static List<UserVO> getStudentListByExcel(Workbook wk) throws ParseException {
		final String GENDER_MAN_SPRING = "男", GENDER_WOMAN_SPRING = "女";
		final Integer GENDER_MAN_INTEGER = 0, GENDER_WOMAN_INTEGER = 1;
		List<UserVO> resultList = new ArrayList<UserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		UserVO vo = null;
		for(int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			vo = new UserVO();
			for(int currentCellNumber = 0; currentCellNumber < maxCellNumber; currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				switch (currentCellNumber) {
				case 0:
					//学好
					vo.setUserId(getValue(cell));
					break;
				case 1:
					//姓名
					vo.setUserName(getValue(cell));
					break;
				case 2:
					//性别
					String gender = getValue(cell);
					if(GENDER_MAN_SPRING.equals(gender)){
						vo.setGender(GENDER_MAN_INTEGER);
					}else if(GENDER_WOMAN_SPRING.equals(gender)){
						vo.setGender(GENDER_WOMAN_INTEGER);
					}
					break;
				case 3:
					//出生年月
					String str = getValue(cell);
					SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
			        java.util.Date d = null;  
			        try {  
			            d = format.parse(str);  
			        } catch (Exception e) {  
			            e.printStackTrace();  
			        }  
			        java.sql.Date date = new java.sql.Date(d.getTime());
					vo.setBirthday(date);
					break;
				case 4:
					//邮箱
					vo.setEmail(getValue(cell));
					break;
				case 5:
					//密码
					vo.setPassword(MD5(getValue(cell)));;
					break;
				case 6:
					vo.setTel(getValue(cell));
					break;
				case 7:
					vo.setAddress(getValue(cell));
					break;
				default:
					break;
				}
			}
			resultList.add(vo);
		}
		return resultList;
	}

	@SuppressWarnings("static-access")
	private static String getValue(Cell cell){
		if(cell == null){
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
		
	}
	
	
	
	
	
	public static String MD5(String pwd){
		try {
            // 生成一个MD5加密计算摘要
            MessageDigest md = MessageDigest.getInstance("MD5");
            // 计算md5函数
            md.update(pwd.getBytes());
            // digest()最后确定返回md5 hash值，返回值为8为字符串。因为md5 hash值是16位的hex值，实际上就是8位的字符
            // BigInteger函数则将8位的字符串转换成16位hex值，用字符串来表示；得到字符串形式的hash值
            String md5=new BigInteger(1, md.digest()).toString(16);
            //BigInteger会把0省略掉，需补全至32位
            return fillMD5(md5);
        } catch (Exception e) {
            throw new RuntimeException("MD5加密错误:"+e.getMessage(),e);
        }
	}
	
	public static String fillMD5(String md5){
        return md5.length()==32?md5:fillMD5("0"+md5);
    }
	
	
}
